﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using DbHelper;//自定义帮助类
using Gx.Model.Entity;//实体
using Gx.Common;//通用类
namespace Gx.Dal.Employee
{
    /// <summary>
    /// 
    /// </summary>
    public partial class E_EmployeeInfoDal : SqlServerHelper, IDisposable
    {


        public E_EmployeeInfo EmployeeLogin(string userLogin, string userPwd)
        {
            E_EmployeeInfo model = null;
            string strSql = @"
SELECT  EmployeeId ,
        EmpName 
FROM    dbo.E_EmployeeInfo WITH ( NOLOCK )
WHERE   ( EmpMobile = @userLogin
          OR EmpNum = @userLogin
        )
        AND EmpPwd = @userPwd
        AND IsWork=1
 ";
            SqlParameter[] p = {
                                           new SqlParameter("@UserLogin", SqlDbType.VarChar,20){Value=userLogin},
                                           new SqlParameter("@UserPwd", SqlDbType.VarChar,50){Value=userPwd}
        };
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql, p))
            {
                int EmployeeId = r.GetOrdinal("EmployeeId");
                int EmpName = r.GetOrdinal("EmpName");

                if (r.Read())
                {
                    model = new E_EmployeeInfo
                    {
                        EmployeeId = r.GetInt32(EmployeeId),
                        EmpName = r.GetString(EmpName)
                    };
                }
                r.Close();
            }
            return model;
        }


        /// <summary>
        /// 重置密码
        /// </summary>
        /// <param name="EmployeeId"></param>
        /// <returns></returns>
        public bool RestPassWord(int EmployeeId, string EmpPwd)
        {
            string strSql = @"UPDATE  dbo.E_EmployeeInfo
SET     EmpPwd = @EmpPwd
WHERE   [Status] = 0
        AND EmployeeId = @EmployeeId
        AND IsWork = 1";
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@EmployeeId", SqlDbType.Int,4) { Value = EmployeeId },
                new SqlParameter("@EmpPwd",SqlDbType.NVarChar,50){ Value=EmpPwd}
    };
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql, pList.ToArray()) > 0;
        }

        public List<E_EmployeeInfo> GetE_EmployeeInfoListLikeFront10(string empName)
        {
            List<E_EmployeeInfo> list = new List<E_EmployeeInfo>();
            string strSql = @"
SELECT  TOP(10) EmployeeId ,
        EmpName ,
        EmpNum ,
        EmpMobile ,
        ISNULL(DepName, '') AS DepName,
        ISNULL(LevelName,'') AS LevelName
FROM    dbo.E_EmployeeInfo WITH ( NOLOCK )
        LEFT JOIN dbo.E_DepartmentInfo WITH(NOLOCK) ON dbo.E_EmployeeInfo.DepId = dbo.E_DepartmentInfo.Id
        LEFT JOIN dbo.E_LevelInfo  WITH(NOLOCK) ON dbo.E_LevelInfo.Id = dbo.E_EmployeeInfo.LevelId
                                        AND dbo.E_EmployeeInfo.[Status] = 0
WHERE   IsWork = 1
        AND ( EmpName LIKE '%' + @EmpName + '%'
              OR EmpNum LIKE '%' + @EmpName + '%'
              OR EmpMobile LIKE '%' + @EmpName + '%');";
            List<SqlParameter> pList = new List<SqlParameter>
            {
                new SqlParameter("@EmpName", SqlDbType.NVarChar, 20) { Value = empName }
            };
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql, pList.ToArray()))
            {
                int EmployeeId = r.GetOrdinal("EmployeeId");
                int EmpNum = r.GetOrdinal("EmpNum");
                int EmpName = r.GetOrdinal("EmpName");
                int EmpMobile = r.GetOrdinal("EmpMobile");
                int DepName = r.GetOrdinal("DepName");
                int LevelName = r.GetOrdinal("LevelName");
                E_EmployeeInfo model = null;
                while (r.Read())
                {
                    model = new E_EmployeeInfo
                    {
                        EmployeeId = r.GetInt32(EmployeeId),
                        EmpNum = r.GetString(EmpNum),
                        EmpName = r.GetString(EmpName),
                        EmpMobile = r.GetString(EmpMobile),
                        DepName = r.GetString(DepName),
                        LevelName = r.GetString(LevelName),
                    };
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public List<E_EmployeeInfo> GetE_EmployeeInfoList(string strWhere)
        {
            List<E_EmployeeInfo> list = new List<E_EmployeeInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append(" EmployeeId");
            strSql.Append(",CityId");
            strSql.Append(",EmpNum");
            strSql.Append(",EmpName");
            strSql.Append(",EmpPwd");
            strSql.Append(",EmpCard");
            strSql.Append(",EmpBirthday");
            strSql.Append(",EmpSex");
            strSql.Append(",EmpAge");
            strSql.Append(",EmpCulture");
            strSql.Append(",EmpMobile");
            strSql.Append(",UrgentName");
            strSql.Append(",UrgentMobile");
            strSql.Append(",EmpProvince");
            strSql.Append(",EmpCity");
            strSql.Append(",EmpArea");
            strSql.Append(",EmpBankType");
            strSql.Append(",EmpBankCard");
            strSql.Append(",HouseholdsType");
            strSql.Append(",HouseholdsAddress");
            strSql.Append(",GoCompanyTime");
            strSql.Append(",PositiveTime");
            strSql.Append(",ProbationaryPeriodSalary");
            strSql.Append(",IsPositive");
            strSql.Append(",DepId");
            strSql.Append(",LevelId");
            strSql.Append(",EmpBasicWage");
            strSql.Append(",SocialSecurityId");
            strSql.Append(",IsSocialSecurity");
            strSql.Append(",IsFundAnteil");
            strSql.Append(",CompanyRatio");
            strSql.Append(",PersonalRatio");
            strSql.Append(",CompanpFundAnteil");
            strSql.Append(",CompanpFundAnteilRatio");
            strSql.Append(",PersonalAnteil");
            strSql.Append(",PersonalAnteilRatio");
            strSql.Append(",IsWork");
            strSql.Append(",ResignationTime");
            strSql.Append(",MarryStatus");
            strSql.Append(",EmpHight");
            strSql.Append(",EmpWeight");
            strSql.Append(",EmpHealth");
            strSql.Append(",EmpJoinJobTime");
            strSql.Append(",EmpNature");
            strSql.Append(",Caption");
            strSql.Append(",Status");
            strSql.Append(",AddTime");
            strSql.Append(",AddUser");
            strSql.Append(",LastEditTime");
            strSql.Append(",LastEditUser");
            strSql.AppendFormat(" FROM E_EmployeeInfo WITH(NOLOCK) WHERE [Status]=0 {0}", strWhere);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int EmployeeId = r.GetOrdinal("EmployeeId");
                int CityId = r.GetOrdinal("CityId");
                int EmpNum = r.GetOrdinal("EmpNum");
                int EmpName = r.GetOrdinal("EmpName");
                int EmpPwd = r.GetOrdinal("EmpPwd");
                int EmpCard = r.GetOrdinal("EmpCard");
                int EmpBirthday = r.GetOrdinal("EmpBirthday");
                int EmpSex = r.GetOrdinal("EmpSex");
                int EmpAge = r.GetOrdinal("EmpAge");
                int EmpCulture = r.GetOrdinal("EmpCulture");
                int EmpMobile = r.GetOrdinal("EmpMobile");
                int UrgentName = r.GetOrdinal("UrgentName");
                int UrgentMobile = r.GetOrdinal("UrgentMobile");
                int EmpProvince = r.GetOrdinal("EmpProvince");
                int EmpCity = r.GetOrdinal("EmpCity");
                int EmpArea = r.GetOrdinal("EmpArea");
                int EmpBankType = r.GetOrdinal("EmpBankType");
                int EmpBankCard = r.GetOrdinal("EmpBankCard");
                int HouseholdsType = r.GetOrdinal("HouseholdsType");
                int HouseholdsAddress = r.GetOrdinal("HouseholdsAddress");
                int GoCompanyTime = r.GetOrdinal("GoCompanyTime");
                int PositiveTime = r.GetOrdinal("PositiveTime");
                int ProbationaryPeriodSalary = r.GetOrdinal("ProbationaryPeriodSalary");
                int IsPositive = r.GetOrdinal("IsPositive");
                int DepId = r.GetOrdinal("DepId");
                int LevelId = r.GetOrdinal("LevelId");
                int EmpBasicWage = r.GetOrdinal("EmpBasicWage");
                int SocialSecurityId = r.GetOrdinal("SocialSecurityId");
                int IsSocialSecurity = r.GetOrdinal("IsSocialSecurity");
                int IsFundAnteil = r.GetOrdinal("IsFundAnteil");
                int CompanyRatio = r.GetOrdinal("CompanyRatio");
                int PersonalRatio = r.GetOrdinal("PersonalRatio");
                int CompanpFundAnteil = r.GetOrdinal("CompanpFundAnteil");
                int CompanpFundAnteilRatio = r.GetOrdinal("CompanpFundAnteilRatio");
                int PersonalAnteil = r.GetOrdinal("PersonalAnteil");
                int PersonalAnteilRatio = r.GetOrdinal("PersonalAnteilRatio");
                int IsWork = r.GetOrdinal("IsWork");
                int ResignationTime = r.GetOrdinal("ResignationTime");
                int MarryStatus = r.GetOrdinal("MarryStatus");
                int EmpHight = r.GetOrdinal("EmpHight");
                int EmpWeight = r.GetOrdinal("EmpWeight");
                int EmpHealth = r.GetOrdinal("EmpHealth");
                int EmpJoinJobTime = r.GetOrdinal("EmpJoinJobTime");
                int EmpNature = r.GetOrdinal("EmpNature");
                int Caption = r.GetOrdinal("Caption");
                E_EmployeeInfo model = null;
                while (r.Read())
                {
                    model = new E_EmployeeInfo();
                    model.EmployeeId = r.GetInt32(EmployeeId);
                    model.CityId = r.GetInt32(CityId);
                    model.EmpNum = r.GetString(EmpNum);
                    model.EmpName = r.GetString(EmpName);
                    model.EmpPwd = r.GetString(EmpPwd);
                    model.EmpCard = r.GetString(EmpCard);
                    model.EmpBirthday = r.GetDateTime(EmpBirthday);
                    model.EmpSex = r.GetByte(EmpSex);
                    model.EmpAge = r.GetByte(EmpAge);
                    model.EmpCulture = r.GetByte(EmpCulture);
                    model.EmpMobile = r.GetString(EmpMobile);
                    model.UrgentName = r.GetString(UrgentName);
                    model.UrgentMobile = r.GetString(UrgentMobile);
                    model.EmpProvince = r.GetInt16(EmpProvince);
                    model.EmpCity = r.GetInt16(EmpCity);
                    model.EmpArea = r.GetInt16(EmpArea);
                    model.EmpBankType = r.GetInt16(EmpBankType);
                    model.EmpBankCard = r.GetString(EmpBankCard);
                    model.HouseholdsType = r.GetByte(HouseholdsType);
                    model.HouseholdsAddress = r.GetString(HouseholdsAddress);
                    model.GoCompanyTime = r.GetDateTime(GoCompanyTime);
                    model.PositiveTime = r.GetDateTime(PositiveTime);
                    model.ProbationaryPeriodSalary = r.GetDecimal(ProbationaryPeriodSalary);
                    model.IsPositive = r.GetBoolean(IsPositive);
                    model.DepId = r.GetInt32(DepId);
                    model.LevelId = r.GetInt32(LevelId);
                    model.EmpBasicWage = r.GetDecimal(EmpBasicWage);
                    model.SocialSecurityId = r.GetInt32(SocialSecurityId);
                    model.IsSocialSecurity = r.GetBoolean(IsSocialSecurity);
                    model.IsFundAnteil = r.GetBoolean(IsFundAnteil);
                    model.CompanyRatio = r.GetDecimal(CompanyRatio);
                    model.PersonalRatio = r.GetDecimal(PersonalRatio);
                    model.CompanpFundAnteil = r.GetDecimal(CompanpFundAnteil);
                    model.CompanpFundAnteilRatio = r.GetDecimal(CompanpFundAnteilRatio);
                    model.PersonalAnteil = r.GetDecimal(PersonalAnteil);
                    model.PersonalAnteilRatio = r.GetDecimal(PersonalAnteilRatio);
                    model.IsWork = r.GetBoolean(IsWork);
                    model.ResignationTime = r.GetDateTime(ResignationTime);
                    model.MarryStatus = r.GetByte(MarryStatus);
                    model.EmpHight = r.GetString(EmpHight);
                    model.EmpWeight = r.GetString(EmpWeight);
                    model.EmpHealth = r.GetString(EmpHealth);
                    model.EmpJoinJobTime = r.GetDateTime(EmpJoinJobTime);
                    model.EmpNature = r.GetByte(EmpNature);
                    model.Caption = r.GetString(Caption);
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }
    }
}
